
# import libraries
from requests import get

import numpy as np 
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

from bs4 import BeautifulSoup
from requests import get
import re
import time

import seaborn as sns
import plotly.express as px


# upload IMLS CSV file
imls = pd.read_csv("awarded-grants-2025-04-11.csv")
                   
imls.head()


# drop log number, description, and supplemental indicator columns
imls = imls.drop(columns=['Log Number', 'Description', 'Supplement Indicator'])


# check for null values
imls.info() # 7 rows in which city and state have null values

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 22425 entries, 0 to 22424
Data columns (total 7 columns):
 #   Column          Non-Null Count  Dtype 
---  ------          --------------  ----- 
 0   Institution     22425 non-null  object
 1   Fiscal Year     22425 non-null  int64 
 2   Program         22425 non-null  object
 3   Federal Funds   22425 non-null  object
 4   Funding Office  22425 non-null  object
 5   City            22418 non-null  object
 6   State           22418 non-null  object
dtypes: int64(1), object(6)
memory usage: 1.2+ MB


# null rows df
imls_null = imls[imls.isnull().any(axis=1)]

# research the institution column to manually populate city and state
null_city = ['Los Angeles', 'Boulder', 'Cullowhee', 'Swarthmore', 'Kipnuk', 'Swarthmore', 'Cullowhee']
null_state = ['CA', 'CO', 'NC', 'PA', 'AK', 'PA', 'NC']

# populate city and state in the imls dataframe
imls.loc[imls_null.index, 'City'] = null_city
imls.loc[imls_null.index, 'State'] = null_state

# check for null values
imls.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 22425 entries, 0 to 22424
Data columns (total 7 columns):
 #   Column          Non-Null Count  Dtype 
---  ------          --------------  ----- 
 0   Institution     22425 non-null  object
 1   Fiscal Year     22425 non-null  int64 
 2   Program         22425 non-null  object
 3   Federal Funds   22425 non-null  object
 4   Funding Office  22425 non-null  object
 5   City            22425 non-null  object
 6   State           22425 non-null  object
dtypes: int64(1), object(6)
memory usage: 1.2+ MB


# drop rows where fiscal year = 2023 and 2024
imls = imls.loc[(imls['Fiscal Year'] != 2023) & (imls['Fiscal Year'] != 2024)]


# drop '$' form federal funds and convert to int
imls['Federal Funds'] = imls['Federal Funds'].str.replace('$', '').astype(int)

/home/tnguye89/BSOS326-Spring25/3. Final Project/ipykernel_24878/2390766458.py:2: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.
  imls['Federal Funds'] = imls['Federal Funds'].str.replace('$', '').astype(int)


# final output of imls dataframe
imls.head(5)


# scrape data
# get request
url = "https://history.house.gov/Institution/Presidents-Coinciding/Party-Government/"
webpage = get(url)

# check status code
webpage.status_code

200


# use BeatifulSoup to parse webpage
content = BeautifulSoup(webpage.content, "html.parser")


# # congress n-th order and time period appears every 5 elements. Last two rows contain time period from 2023-2027. split congress column into: congress: n-th congress and year: time span
congress_col = [i.get_text().split(' ') for i in content.select('tbody tr td')[::5]][:-2]


#  split congress column and repeat values twice for since one Congressional term spans two years
congress = pd.Series([i[0] for i in congress_col]).repeat(2)


# split year column. Remove () and repeat values twice to match congress column length
year = pd.Series([i[1].replace('(', '').replace(')', '') for i in congress_col]).repeat(2)


# house majority column and repeat values twice to match congress column length
house = pd.Series([i.get_text() for i in content.select('tbody tr td')[1::5]][:-2]).repeat(2)


# certain values in house column contains numbers due to superscript in the webpage
# remove these numbers
house = [re.sub(r"\d", "", i) for i in house]


# senate majority column and repeat values twice to match congress column length
senate = pd.Series([i.get_text() for i in content.select('tbody tr td')[2::5]][:-2]).repeat(2)


# certain values in senate column contains numbers due to superscript in the webpage
# remove these numbers
senate = [re.sub(r"\d", "", i) for i in senate]

# 12th value in senate column has additional characters that is not needed. Remove these characters
senate[11] = senate[11].replace('\xa0', '')


# president party column and repeat values twice to match congress column length
pres = pd.Series([i.get_text() for i in content.select('tbody tr td')[3::5]][:-2]).repeat(2)


# extract party from pres column
pres = pres.apply(lambda x: re.findall(r'Democrat|Republican', x))


# replace []
pres = pd.Series(["".join(i) for i in pres])


# change 'RepublicanDemocrat' to 'Republican/Democrat' as a single value
pres[7:10] = 'Republican/Democrat'


# party government column and repeat values twice to match congress column length
party_gov = pd.Series([i.get_text() for i in content.select('tbody tr td')[4::5]][:-2]).repeat(2)


# certain values in party gov column contains numbers due to superscript in the webpage
# remove these numbers
party_gov = [re.sub(r"\d", "", i) for i in party_gov]


# congressional party breakdown df
congress_df = pd.DataFrame({'Congress': congress,
                            'Year': year,
                            'House': house,
                            'Senate': senate,
                            'Party Government': party_gov}
                          ).reset_index()


# congress df presidency column
congress_df['Presidency'] = pres


# drop in index column
congress_df = congress_df.drop(columns='index', axis=1)


# final output of congress dataframe
congress_df.head(10)


# replace year column values with incrementing years matching congress term (ie: 1857 and 1858 would be corresponding to 35th congress term)
# list of years from 1857-2022 since the 117th Congressional term ended on January 2023 but ignored for simplicity sake. See note in introduction section
year_list = range(1857, 2023) 
congress_df['Year'] = year_list


# congress df for year 1997 and forward to match with imls df
congress_df = congress_df.loc[congress_df['Year'] >= 1997]


# final output of congress dataframe
congress_df.head(5)


# merge imls and congress_df to a new df on fiscal year(imls) and year (congress_df) column
final_df = imls.merge(congress_df, left_on='Fiscal Year', right_on='Year')


# drop year column and only use fiscal year column
final_df = final_df.drop('Year', axis=1)


# final working dataframe
final_df.head(5)


# Total funding by year
funding_by_year = final_df.groupby('Fiscal Year')['Federal Funds'].sum()

funding_by_year_df = pd.DataFrame({'Funding by Year': funding_by_year
                                  }).reset_index()


# 1. What was the trend in total funding from 1997-2022?
fig = px.line(funding_by_year_df, x='Fiscal Year', y='Funding by Year', title='IMLS Funding by Fiscal Year<br><sup>(In Millions)</sup>', markers=True, height=550)

fig.update_xaxes(tickvals=funding_by_year_df['Fiscal Year'])

fig.show()


# 2. What was the funding distribution from 1997-2022?
fig = px.box(funding_by_year_df, x='Funding by Year', title='IMLS Funding Distribution<br><sup>(In Millions)</sup>')

fig.update_layout(xaxis_title='Funding Distribution',
                 width=150)

fig.show()


# Top 10 cities in funding 
# groupby city and state since many states have the same cities name
top_cities_funding = final_df.groupby(['City', 'State'])['Federal Funds'].sum().sort_values(ascending=False).head(10)


top_cities_df = pd.DataFrame({'Funding': top_cities_funding}).reset_index()

# combine city and state to one column for clarity
top_cities_df['City_State'] = top_cities_df['City'] + ", " + top_cities_df['State']


# Top 10 states in funding 
top_states_funding = final_df.groupby('State')['Federal Funds'].sum().sort_values(ascending=False).head(10)


top_states_df = pd.DataFrame({'Funding': top_states_funding}).reset_index()


# 3. What was the top 10 cities vs states in funding from 1997-2022?
fig = px.bar(top_cities_df, x='Funding', y='City_State', orientation='h', 
             labels={'City_State': 'City, State'}, 
             title='Top 10 Cities in IMLS Funding<br><sup>(In Millions)</sup>',
             color='Funding',
             color_continuous_scale='algae')

fig.update_layout(height=500,
                  plot_bgcolor='#f7f7f5')

fig.show()


# 3. What was the top 10 cities vs states in funding from 1997-2022?
fig = px.bar(top_states_df, x='Funding', y='State', orientation='h', title='Top 10 States in IMLS Funding<br><sup>(In Millions)</sup>',
             color='Funding',
             color_continuous_scale='algae')

fig.update_layout(height=500,
                  plot_bgcolor='#f7f7f5')

fig.show()


# top programs in funding
top_programs_funding = final_df.groupby('Program')['Federal Funds'].sum().sort_values(ascending=False).head(10)

top_programs_df = pd.DataFrame({'Funding': top_programs_funding}).reset_index()


# 4. What was the top 10 programs that received the most funding?
fig = px.bar(top_programs_df, x='Funding', y='Program', orientation='h', title='Top 10 Programs in IMLS Funding<br><sup>(In Billions)</sup>',
             color='Funding',
             color_continuous_scale='haline')

fig.update_layout(height=500,
                  plot_bgcolor='#f7f7f5')

fig.show()


# funding by office
funding_by_office = final_df.groupby('Funding Office')['Federal Funds'].sum()

funding_by_office_df = pd.DataFrame({'Funding': funding_by_office}).reset_index()


# 5. Which funding office received the most funding?
fig = px.bar(funding_by_office_df, x='Funding', y='Funding Office', orientation='h', title='IMLS Funding for Different Funding Office<br><sup>(In Billions)</sup>',
             color='Funding Office')

fig.update_layout(height=500,
                  plot_bgcolor='#f7f7f5')

fig.show()


# get house party value count
house_party_count = congress_df['House'].value_counts() # use congress df due to matching row for year and house only occurs once

house_party_df = pd.DataFrame({'Value Count': house_party_count}).reset_index().rename(columns={'index': 'Party'}) # change index column to party


# get senate party value count
senate_party_count = congress_df['Senate'].value_counts() # use congress df due to matching row for year and senate only occurs once

senate_party_df = pd.DataFrame({'Value Count': senate_party_count}).reset_index().rename(columns={'index': 'Party'}) # change index column to party


# 6. How many Democrat/Republican Congress (House and Senate) were there? 
fig = px.bar(house_party_df, x='Party', y='Value Count', title='House of Representative Majority Party Value Count',
             color='Party',
             color_discrete_map={'Republicans': 'red',
                                'Democrats': 'blue'})

fig.update_layout(height=550,
                  width=750,
                  plot_bgcolor='#f7f7f5')

fig.show()


# 6. Which political party dominates Congress (House and Senate) the most? 
fig = px.bar(senate_party_df, x='Party', y='Value Count', title='Senate Majority Party Value Count',
             color='Party',
             color_discrete_map={'Republicans': 'red',
                                'Democrats': 'blue'})

fig.update_layout(height=550,
                  width=750,
                  plot_bgcolor='#f7f7f5')

fig.show()


# get President's party value count
pres_count = congress_df['Presidency'].value_counts()

pres_df = pd.DataFrame({'Value Count': pres_count}).reset_index().rename(columns={'index': 'Presidency'}) # change index column to party


# 7. How many Democrat/Republican President were there? 
fig = px.bar(pres_df, x='Presidency', y='Value Count', title="President's Political Party Value Count",
             color='Presidency',
             color_discrete_map={'Republican': 'red',
                                'Democrat': 'blue',
                                'Republican/Democrat': 'yellow'})

fig.update_layout(height=550,
                  width=750,
                  plot_bgcolor='#f7f7f5')

fig.show()


# percent of time party gov have unified control
party_prop = congress_df['Party Government'].value_counts(normalize=True)

# convert proportions to percentages
party_perc = round((party_prop * 100), 2)

party_prop_df = pd.DataFrame({'Percentage': party_perc}).reset_index().rename(columns={'index': 'Party Government'}) # change index column to party gov


# 8. What percent of the time did the party government have unified control?
fig = px.bar(party_prop_df, x='Party Government', y='Percentage', title='Party Government Unified Control Frequency',
             color='Party Government',
             color_discrete_map={'Unified': 'red',
                                'Divided': 'blue',
                                 'Unified / Divided': 'yellow'})

fig.update_layout(height=550,
                  width=750,
                  plot_bgcolor='#f7f7f5')

fig.show()


# funding by party government
funding_gov = final_df.groupby('Party Government')['Federal Funds'].sum()

funding_gov_df = pd.DataFrame({'Funding': funding_gov}).reset_index()


# 9. Did unified party government received more funding than divided party government?
# 9a. If unified, which political party accounted for the most funding?
fig = px.bar(funding_gov_df, x='Party Government', y='Funding', title='Funding by Party Government<br><sup>(In Billions)</sup>',
             color='Party Government',
             color_discrete_map={'Unified': 'red',
                                'Divided': 'blue',
                                 'Unified / Divided': 'yellow'})

fig.update_layout(height=550,
                  width=750,
                  plot_bgcolor='#f7f7f5')

fig.show()


# funding by state
funding_states = final_df.groupby('State')['Federal Funds'].sum()

funding_states_df = pd.DataFrame({'Funding': funding_states}).reset_index()


# 10. What is the national funding distribution? 
fig = px.choropleth(funding_states_df, color='Funding', locations='State', locationmode='USA-states', 
                    scope='usa', 
                    title='Funding Distribution Nationally<br><sup>(In Millions)</sup>')

fig.update_layout(height=550,
                  width=1000)

fig.show()

	Log Number	Description	Institution	Fiscal Year	Program	Federal Funds	Funding Office	City	State
0	RE-256655-OLS-24	The libraries at Virginia Tech and the Univers...	Virginia Polytechnic Institute (Virginia Polyt...	2024	Laura Bush 21st Century Librarian Program	$115398	Office of Library Services	Blacksburg	VA
1	IGSM-256224-OMS-24	Historic Cherry Hill will develop programming ...	Historic Cherry Hill	2024	Inspire! Grants for Small Museums	$47469	Office of Museum Services	Albany	NY
2	NAB-256413-OLS-24	The Village of Aniak will support a librarian ...	Village of Aniak	2024	Native American Library Services: Basic Grants	$9878	Office of Library Services	Aniak	AK
3	NAB-256441-OLS-24	The La Jolla Band of Luiseno Indians will deli...	La Jolla Band of Luiseño Indians	2024	Native American Library Services: Basic Grants	$10000	Office of Library Services	Pauma Valley	CA
4	RE-256709-OLS-24	The University of Illinois Urbana-Champaign Sc...	University of Illinois (University of Illinois...	2024	Laura Bush 21st Century Librarian Program	$578677	Office of Library Services	Urbana	IL

	Institution	Fiscal Year	Program	Federal Funds	Funding Office	City	State
1231	Hoopa Valley Tribe	2022	Native American Library Services: Basic Grants	10000	Office of Library Services	Hoopa	CA
1232	Yavapai-Prescott Indian Tribe	2022	Native American Library Services: Enhancement ...	145881	Office of Library Services	Prescott	AZ
1233	Pawnee Nation of Oklahoma	2022	Native American Library Services: Enhancement ...	150000	Office of Library Services	Pawnee	OK
1234	Illinois State University Milner Library	2022	Save America's Treasures	29294	Office of Museum Services	Normal	IL
1235	San Francisco Art Institute	2022	Save America's Treasures	34100	Office of Museum Services	San Francisco	CA

Library and Museum Funding by Party Government Breakdown¶

Introduction¶

Data¶

Description of data, variables studied, and data obtain methods¶

Data Processing¶

Visualizations¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Analysis¶

Reference¶

	Congress	Year	House	Senate	Party Government	Presidency
0	35th	1857–1859	Democrats	Democrats	Unified	Democrat
1	35th	1857–1859	Democrats	Democrats	Unified	Democrat
2	36th	1859–1861	Republicans	Democrats	Divided	Democrat
3	36th	1859–1861	Republicans	Democrats	Divided	Democrat
4	37th	1861–1863	Republicans	Republicans	Unified	Republican
5	37th	1861–1863	Republicans	Republicans	Unified	Republican
6	38th	1863–1865	Republicans	Republicans	Unified	Republican
7	38th	1863–1865	Republicans	Republicans	Unified	Republican/Democrat
8	39th	1865–1867	Republicans	Republicans	Unified/ Divided	Republican/Democrat
9	39th	1865–1867	Republicans	Republicans	Unified/ Divided	Republican/Democrat

	Congress	Year	House	Senate	Party Government	Presidency
140	105th	1997	Republicans	Republicans	Divided	Democrat
141	105th	1998	Republicans	Republicans	Divided	Democrat
142	106th	1999	Republicans	Republicans	Divided	Democrat
143	106th	2000	Republicans	Republicans	Divided	Democrat
144	107th	2001	Republicans	Republicans / Democrats	Unified / Divided	Republican